<?php
class Model_baza extends Model {
	function Model_baza() {
		parent::Model();
	}

	function bringBaza($aData, $aWhere) {

		$this -> db -> start_cache();
		if (!isset($aData["export"])) {
			$this -> db -> select("  persons.person_id,persons.lname,persons.fname,persons.sex,persons.position_id, persons.position_sub_id, city_id, company, persons.client_id,date_modified,available, priority, obs, address ", false);
		}

		if (isset($aData["export"]) && $aData["export"] == 1) {
			$this -> db -> select("persons.*,domain.name as domain_name,city.name as city_name,county.name as county_name,position.name as position_name,division.name as division_name,  P.name as subposition_name,D.name as subdomain_name, T.name as trust_name ", false);
			//$this->db->join("client_persons_group", "client_persons_group.person_id=persons.person_id", "LEFT");
			$this -> db -> join("domain", "domain.domain_id=persons.domain_id", "left");
			$this -> db -> join("domain D", "persons.domain_sub_id=D.domain_id  ", "left");
			$this -> db -> join("city", "persons.city_id=city.city_id ", "left");
			$this -> db -> join("division", "division.division_id=persons.division_id", "left");
			$this -> db -> join("county", "persons.county_id=county.county_id", "left");
			$this -> db -> join("`position`", "persons.position_id=position.position_id  ", "left");
			$this -> db -> join("`position` P", "persons.position_sub_id=P.position_id  ", "left");
			$this -> db -> join("trust T", "T.trust_id=persons.trust_id", "left");
		}

		if ($aData["client_id"] != "") {
			$this -> db -> join("(select DISTINCT(person_id),`client_id`  from `client_persons` where `client_persons`.`extern` = 0 AND client_id in (" . str_replace("=", ",", $aData["client_id"]) . ") ) CC", "CC.person_id=persons.person_id", "inner");
		}
		$this -> db -> where($aWhere, null, false);

		if (!empty($aData["limit"])) {
			$this -> db -> limit($aData["limit"], $aData["start"]);
		}
		if (!empty($aData["sort"])) {
			$this -> db -> order_by($aData["sort"], $aData["dir"]);
		}

		$query = $this -> db -> get("persons");
		$result = $query -> result_array();
		$totalCount = $this -> db -> count_all_results("persons");
		$this -> db -> flush_cache();
		$data = array('error' => false, 'totalCount' => $totalCount, 'DS_PERSONS' => $result);
		return $data;
	}

	function bringBazaDubluri($aData, $aWhere) {
		$join_dbl = $select_dbl = "";
		if (!isset($aData["export"])) {
			$select_dbl = " Select SQL_CALC_FOUND_ROWS  persons.person_id,persons.lname, persons.fname,persons.sex,persons.position_id, persons.position_sub_id, persons.city_id, persons.company, persons.client_id, persons.modify_by, persons.date_modified, persons.available, persons.priority, persons.obs,CONCAT(persons.address,' ',persons.address2) as address ";
		}
		if (isset($aData["export"]) && $aData["export"] == 1) {
			$select_dbl = "Select persons.*,client.name as client_name,persons.date_modified,persons.company,send_present,domain.name as domain_name,city.name as city_name,county.name as county_name,position.name as position_name,division.name as division_name,CONCAT(U.lname, \"  \" ,U.fname) as modify_user, client_persons_group.clients_id,
                    CONCAT(U2.lname,' ',U2.fname) as create_user,CONCAT( U1.lname, '  ', U1.fname ) as modify_user1,priority,
                    persons.date_created,P.name as subposition_name,D.name as subdomain_name ";
			$join_dbl = "
                                /*INNER JOIN client_persons_group on client_persons_group.person_id=persons.person_id */
                                LEFT JOIN domain on domain.domain_id=persons.domain_id
                                LEFT JOIN domain D on persons.domain_sub_id=D.domain_id
                                LEFT JOIN client on client.client_id=persons.client_id
                                LEFT JOIN city on  persons.city_id=city.city_id
                                LEFT JOIN division on  division.division_id=persons.division_id
                                LEFT JOIN `position` on `position`.position_id=persons.position_id
                                LEFT JOIN `position` P on persons.position_sub_id=P.position_id
                                LEFT JOIN county on persons.county_id=county.county_id
                                LEFT JOIN user U on U.user_id=persons.modify_by
                                LEFT JOIN user U1 on U1.user_id=persons.modify_by2
                                LEFT JOIN user U2 on U2.user_id=persons.create_by";
		}

		if (!empty($aData["limit"])) {
			$limit = " LIMIT {$aData["start"]},  {$aData["limit"]} ";
		}

		$query = $this -> db -> query("{$select_dbl}
                        FROM persons
                        INNER JOIN
                            (SELECT address,person_id,lname,fname,COUNT(*) FROM persons where available='1' GROUP BY address,lname,fname HAVING COUNT(*)>1 ) as t2
                            ON  persons.address = t2.address and persons.lname=t2.lname and persons.fname=t2.fname
                            {$join_dbl}
                        where {$aWhere}  ORDER BY lname asc,fname asc {$limit} ");

		$result = $query -> result_array();
		$totalCount = $this -> db -> query('SELECT FOUND_ROWS() total_rows') -> row() -> total_rows;

		$data = array('error' => false, 'totalCount' => $totalCount, 'DS_PERSONS' => $result);

		return $data;
	}

	function getWhere($aData) {

		$filtru = ' 1=1 ';

		if (isset($aData['client_id_not']) && $aData['client_id_not'] != '') {
			$filtru .= " AND persons.person_id not in ( SELECT DISTINCT(person_id) from client_persons where  client_id in ( " . implode(",", explode("=", $aData['client_id_not'])) . "  )  ) ";
		}

		if (isset($aData['name']) && $aData['name'] != '') {
			$name = $this -> db -> escape("%" . $aData['name'] . "%");
			$filtru .= " AND persons.lname like {$name} ";
		}

		if (isset($aData['fname']) && $aData['fname'] != '') {
			$name = $this -> db -> escape("%" . $aData['fname'] . "%");
			$filtru .= " AND persons.fname like {$name} ";
		}
		if (isset($aData['company']) && $aData['company'] != '') {
			$company = $this -> db -> escape("%" . $aData['company'] . "%");
			$filtru .= " AND persons.company like {$company}   ";
		}

		if (isset($aData['email']) && $aData['email'] != '') {
			$email = $this -> db -> escape("%" . $aData['email'] . "%");
			$filtru .= " AND (persons.email like {$email}  OR  persons.email2 like {$email} ) ";
		}

		if (isset($aData['address']) && $aData['address'] != '') {
			$address = $this -> db -> escape("%" . $aData['address'] . "%");
			$filtru .= " AND persons.address like {$address}  ";
		}


		$columns = array('city_id', "county_id", "domain_id", "domain_sub_id", "position_id", "position_sub_id", "sex", "priority", "income", "age", "division_id", "zona", "rate", "periodicitate",  "available");

		foreach ($columns as $val) {
			if (isset($aData[$val]) && $aData[$val] != '') {
				$col = str_replace("=", ",", $aData[$val]);
				$filtru .= " AND ( persons.{$val} in ({$col})  ) ";
			}
		}

		if (isset($aData['letter']) && $aData['letter'] != '') {
			$filtru .= " and(  ";
			$litere = $aData['letter'];
			for ($i = 0; $i < strlen($litere); $i++) {
				if ($litere[$i] != '-')
					$filtru .= " lower(persons.company) REGEXP  '^" . strtolower($litere[$i]) . ".*' or";
				if ($litere[$i] == '-')
					$filtru .= " lower(persons.company) NOT REGEXP  '^[a-z]+'  or";
			}
			$filtru = substr($filtru, 0, -2);
			$filtru .= " ) ";
		}

		$createdfrom = isset($aData['createdfrom']) ? $aData['createdfrom'] : "";
		//creat_de_la
		$modfrom = isset($aData['modfrom']) ? $aData['modfrom'] : "";
		//creat_de_la
		$end = isset($aData['end']) ? $aData['end'] : "";
		$user = isset($aData['user']) ? str_replace("=", ",", $aData['user']) : "";

		if (($modfrom != '') && ($createdfrom != '')) {
			if ($end != '') {
				$filtru_pana_la_mod = " and persons.date_modified <= '{$end} 59:59:59' ";
				$filtru_pana_la_creat = " and persons.date_created <= '{$end} 59:59:59' ";
			}
			if ($user != '') {
				$filtru_pana_la_mod_pers = " and persons.modify_by in  ({$user})   ";
				$filtru_pana_la_creat_pers = " and persons.create_by  in  ({$user}) ";
			}
			$filtru .= " and  (( persons.date_modified >= '{$modfrom} 00:00:00'  " . $filtru_pana_la_mod_pers . " " . $filtru_pana_la_mod . "  ) OR ( persons.date_created >= '{$createdfrom} 00:00:00' " . $filtru_pana_la_creat_pers . " " . $filtru_pana_la_creat . " ))
         ";
		}

		if (($modfrom != '') && ($createdfrom == '')) {
			if ($end != '')
				$filtru_pana_la_mod = " and persons.date_modified <= '{$end} 59:59:59' ";
			if ($user != '')
				$filtru_pana_la_mod_pers = " and persons.modify_by in  ({$user})   ";

			$filtru .= " and ( persons.date_modified >=  '{$modfrom} 00:00:00'  " . $filtru_pana_la_mod_pers . " " . $filtru_pana_la_mod . "   ) ";
		}

		if (($createdfrom != '') && ($modfrom == '')) {
			if ($end != '')
				$filtru_pana_la_creat = " and persons.date_created <= '{$end} 59:59:59' ";
			if ($user != '')
				$filtru_pana_la_creat_pers = " and persons.create_by  in  ({$user}) ";

			$filtru .= " and ( persons.date_created >= '{$createdfrom} 00:00:00'   " . $filtru_pana_la_creat_pers . " " . $filtru_pana_la_creat . " )";
		}

		if (($modfrom == '') && ($createdfrom == ''))
			if ($end != '') {
				$filtru_pana_la_mod = " persons.date_modified <= '{$end} 59:59:59' ";
				$filtru_pana_la_creat = "  persons.date_created <= '{$end} 59:59:59' ";

				if ($user != '') {
					$filtru_pana_la_mod_pers = " and persons.modify_by in  ({$user})   ";
					$filtru_pana_la_creat_pers = " and persons.create_by  in  ({$user}) ";
				}
				$filtru .= " and  ((  " . $filtru_pana_la_mod . " " . $filtru_pana_la_mod_pers . "  ) OR ( " . $filtru_pana_la_creat . " " . $filtru_pana_la_creat_pers . "  ))";
			}

		return $filtru;

	}

}
